06 存储引擎介绍(表类型)

共计 3083 个字符，预计需要花费 8 分钟才能阅读完成。

一. 存储引擎介绍

1. 什么是存储引擎(表类型)

在讲存储引擎之前我们来做个比喻 : 现实生活中我们我们存储的数据文件有不同的类型, 每种文件类型对应各自不同的处理机制

• 比如一个视屏文件, 可以转换成mp4、avi、wmv, 一个图片可以是png、jpg

• 我们电脑的磁盘上也会存在不同类型的文件系统, Windows里常见的 ntfs、fat32，Linux 里常见的 ext3、ext4、xfs 等

• 数据库中的表也应该有不同的类型, 表的类型不同, 会对应 mysql 不同的存取机制, 表类型 又称为 存储引擎

2. 为什么有多种存储引擎

如同上面的比喻, 虽然一个视频文件的类型不一样, 但是呈现的内容都是一样的

• 表数据也是这样, 一张表的数据无论使用什么存储引擎, 用户能看到的数据是一样的, 不同的储引擎存取, 引擎功能, 占用空间大小, 读取性能等可能有区别

• 说白了, 存储引擎就是在如何存储数据、提取数据、更新数据等技术方法的实现上, 底层的实现方式不同, 那么就会呈现出不同存储引擎有着一些自己独有的特点和功能, 对应着不同的存取机制

• 于是乎, 用户在使用的时候根据自己不同的业务场景选择不同的存储引擎, 其中 MySQL 最常用的存储引擎为：MyISAM和InnoDB (下面详细介绍)

• ps : 在 Oracle 和 SQL Server 等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的

3.MySQL 的工作流程

二.Mysql 四种主要的存储引擎

1.InnoDB

• Mysql 5.5 版本及之后的 默认存储引擎
• 优点 : 支持事务, 行级锁定和外键约束对数据的安全性较高
• 特点 : 行锁设计、支持外键, 并支持类似 Oracle 的非锁定读, 即默认 读取操作不会产生锁
• 缺点 : 相对于MyISAM 速度较低, 但安全性较高
• 存储结构 : 每个InnoDB(索引引擎, 自带索引) 在磁盘上存储成 2 个文件，其中文件名和表名都相同

t01.frm  # 存放的是表结构
t01.idb  # 存放的是 索引 + 数据

2.MyISAM

• Mysql 5.5 版本之前的 默认存储引擎
• 优点 : 访问速度相对Innodb 更快
• 缺点 : 不支持事务, 也不支持外键, 对事务完整性没有要求, 数据的安全性没有Innodb 高
• 存储结构 : 每个MyISAM 在磁盘上存储成 3 个文件, 其中文件名和表名都相同

t01.frm  # 表结构
t01.MYD  # MyDaata 存放的是数据
t01.MYI  # MyIndex 存放的是索引

3.Memory

• 优点 : 采用内存存储, 数据的读取非常快
• 缺点 : 数据库重启或发生崩溃, 表中的数据都将消失
• 存储结构 : 每个Memory 在磁盘上存储成 1 个文件，其中文件名和表名都相同

t01.frm  # 表结构

4.Blackhole

• 特点 : 黑洞, 无论存什么都会消失, 类似Linux 中的 dev/null
• 应用 : 可以应用于主备复制中的分发主库

三.MyISAM 与 InnoDB 的区别

1. 存储结构不同

• 每个 MyISAM 在磁盘上存储成三个文件
• 每个 InnoDB 在磁盘上存储成 2 个文件

2. 存储空间

• MyISAM可被压缩, 存储空间较小
• InnoDB需要更多的内存和存储, 它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引

3. 事物支持

• MyISAM强调的是性能, 每次查询具有原子性, 其执行速度比 Innodb 类型更快, 但是不提供事物支持
• InnoDB提供事务支持, 外部键等高级数据库功能, 具有事务 (commit)、回滚(rollback) 和崩溃修复能力 (crach recovery capabilities) 的事务安全 (transaction-safe ACID compliant) 型表

4. 外键

• InnoDB支持外键, MyISAM不支持

5. 增删改查操作

• 如果执行大量的 select, MyISAM 是更好的选择 (因为没有支持行级锁), 在增删的时候需要锁定整个表格, 效率会低一些, 而innoDB 支持行级锁, 删除插入的时候只需要锁定该行就行，效率较高;
• 如果你的数据执行大量的 insert 或update, 出于性能方面的考虑, 应该使用 InnoDB 表, Delete从性能上 Innodb 更优
• 但 delete from table 时，InnoDB不会重新建立表，而是一行一行的删除，在 innodb 上如果要清空保存有大量数据的表，最好使用 truncate table 这个命令

6. 应用

• MyISAM适合查询以及插入为主的应用
• InnoDB适合频繁修改以及涉及到安全性较高的应用

四. 其他存储引擎(了解)

5.NDB 存储引擎

2003 年,MySQL AB 公司从 Sony Ericsson 公司收购了 NDB 存储引擎。NDB 存储引擎是一个集群存储引擎, 类似于 Oracle 的 RAC 集群, 不过与 Oracle RAC 的 share everything 结构不同的是, 其结构是 share nothing 的集群架构, 因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中 (从 5.1 版本开始, 可以将非索引数 据放在磁盘上), 因此主键查找(primary key lookups) 的速度极快, 并且能够在线添加 NDB 数据存储节点 (data node) 以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、高性能、高可扩展性的数据库集群系统, 其面向的也是 OLTP 的数据库应用类型。

6.Infobright 存储引擎

第三方的存储引擎。其特点是存储是按照列而非行的, 因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/, 上面有不少成功的数据 仓库案例可供分析。

7.NTSE 存储引擎

网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性, 不久的将来会实现面向内存的事务支持。

ps : Mysql 支持的存储引擎非常多, 上面只是常见存储引擎, 还支持自定义, 也就是自己写一个

五. 不同存储引擎的使用对比

1. 查看存储引擎

show engines;  # 查看安装的 mysql 所有支持的存储引擎
show variables like 'storage_engine%';  # 查看当前正在使用的存储引擎

2. 创建不同的表并指定不同的存储引擎

create table innodb_t01(id int) engine=innodb;        # 末尾指定 InnoDB 存储引擎
create table myisam_t02(id int) engine=myisam;        # 指定 MyISAM 存储引擎
create table memory_t03(id int) engine=memory;        # 指定 memory 存储引擎
create table blackhole_t04(id int) engine=blackhole;  # 指定 Blackhole 存储引擎

3. 分别向四个表中插入数据

insert into innodb_t01 values(1),(2);   # 插入 ID 1 和 2 , 下同
insert into myisam_t02 values(1),(2);
insert into memory_t03 values(1),(2);
insert into blackhole_t04 values(1),(2);

4. 查看四张表的内容变化

select * from innodb_t01;
select * from myisam_t02;
select * from memory_t03;
select * from blackhole_t04;  # 注意 : 黑洞

结论 : 对于 blackhole 类型的表, 往表内插入任何数据, 都相当于丢入黑洞, 表内永远不存记录

5. 重启 Mysqld 查看 memory 表的变化

• 重启 mysql

• 再次查看 memory_t03 表

结论 : 对于 memory 类型的表，在重启 mysql 或者重启机器后，表内数据清空

六. 表的详细操作

下一篇将详细介绍表的操作